﻿using System;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using EntityModel;

namespace DataAccess
{
    public class ExecuteHelper<T, U> : ExecuteHelperBase<T, U>
        where T : EntityBase, new()
        where U : UserModel
    {
        struct ConnectionInfo
        {
            public string Server;
            public string Database;
            public string UserID;
            public string Password;
        }

        public override T Insert(T entity, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            entity.CreatedBy = UserInfo.Key;
            entity.CreatedDate = DateTime.Now;
            entity.UpdatedBy = UserInfo.Key;
            entity.UpdatedDate = entity.CreatedDate;

            this.objContext.Set<T>().Add(entity);
            this.objContext.SaveChanges();

            return entity;
        }

        public override T Update(T entity, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            var objEntity = this.objContext.Set<T>().Find(entity.Key);
            if (objEntity != null)
            {
                entity.UpdatedBy = UserInfo.Key;
                entity.UpdatedDate = entity.CreatedDate;

                this.objContext.Entry<T>(objEntity).CurrentValues.SetValues(entity);
                this.objContext.SaveChanges();
            }

            return entity;
        }

        public override T Delete(T entity, U UserInfo)
        {
            return DeleteByKey(entity.Key, UserInfo);
        }

        public override T DeleteByKey(Int32 key, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            var objEntity = this.objContext.Set<T>().Find(key);
            if (objEntity != null)
            {
                this.objContext.Set<T>().Remove(objEntity);
                this.objContext.SaveChanges();
            }

            return objEntity;
        }

        public override Result<T> ExecuteQuery(string procedureName, U UserInfo, params ObjectParameter[] parameters)
        {
            var result = new Result<T>();

            //skip checking authorization for executing the store procedure
            if (!this.IsAuthorized<T>(UserInfo)) return result;

            if (parameters == null) parameters = new ObjectParameter[0];

            ObjectContext context = ((IObjectContextAdapter)objContext).ObjectContext;
            result.EntityList = context.ExecuteFunction<T>(procedureName, parameters).ToList();

            var totalRecordParam = parameters.Where(p => p.Name.ToUpper() == "TOTALRECORDS").FirstOrDefault();
            if (totalRecordParam != null) result.TotalRecords = Convert.ToInt32(totalRecordParam.Value);

            return result;
        }

        public override Result<DataRow> ExecuteQueryToDataRow(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {   
            //initialize dataset to contain data
            DataSet dsResult = new DataSet();
            
            //initialize connection to database
            var connectionString = ConnectionStringParser(this.objContext.Database.Connection.ConnectionString);
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
            {
                DataSource = connectionString.Server,
                InitialCatalog = connectionString.Database,
                IntegratedSecurity = true,
                UserID = connectionString.UserID,
                Password = connectionString.Password
            };

            //open connection, execute store procedure and fill data to dataset
            SqlConnection con = new SqlConnection(sqlBuilder.ToString());
            {
                con.Open();

                SqlCommand command = new SqlCommand
                {
                    CommandType = CommandType.StoredProcedure,
                    CommandText = procedureName,
                    Connection = con,
                    CommandTimeout = Common.Variables.TimeOut
                };
                command.Parameters.AddRange(parameters);
                
                SqlDataAdapter adapter = new SqlDataAdapter(command);                
                adapter.Fill(dsResult);

                adapter = null;
                command = null;
                con.Close();
                con = null;
            }

            //fill data to result object
            var result = new Result<DataRow>();
            result.EntityList = dsResult.Tables[0].Rows.Cast<DataRow>().ToList();
            var totalRecordParam = parameters.Where(p => p.ParameterName.ToUpper() == "TOTALRECORDS").FirstOrDefault();
            if (totalRecordParam != null) result.TotalRecords = Convert.ToInt32(totalRecordParam.Value);

            //return the result
            return result;
        }

        public override bool ExecuteNonQuery(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            try
            {
                var result = this.objContext.Database.ExecuteSqlCommand(procedureName, parameters);

                return true;
            }
            catch
            {
                return false;
            }            
        }

        public override int ExecuteScalar(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            if (parameters == null) parameters = new SqlParameter[0];
            
            ObjectContext context = ((IObjectContextAdapter)objContext).ObjectContext;

            var result = context.ExecuteStoreCommand(procedureName, parameters);

            return result;
        }

        #region "private methods"

        private MethodInfo GetFunctionInfo(string functionName)
        {
            return typeof(LMSEntities).GetMethod(functionName);
        }

        private ConnectionInfo ConnectionStringParser(string entityConnectionString)
        {
            var propertyList = entityConnectionString.Split(";".ToCharArray());
            var result = new ConnectionInfo();

            //server name
            var serverName = propertyList.Where(t => t.StartsWith("data source=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(serverName))
            {
                result.Server = serverName.Replace("data source=", "");
            }

            //database name
            var databaseName = propertyList.Where(t => t.StartsWith("initial catalog=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.Database = databaseName.Replace("initial catalog=", "");
            }

            //user id
            var userID = propertyList.Where(t => t.StartsWith("user id=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.UserID = userID.Replace("user id=", "");
            }

            //password
            var password = propertyList.Where(t => t.StartsWith("password=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.Password = password.Replace("password=", "");
            }

            return result;
        }

        #endregion
    }
}
